package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.Department;

public class DepartmentDAO {
    private Connection conn;

    public DepartmentDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public boolean insert(@NonNull String name) throws SQLException {
        if (name.length() < 1) {
            return false;
    	}
        PreparedStatement ps = conn.prepareStatement("INSERT INTO DEPARTMENTS VALUES(?)");
        ps.setString(1, name);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public Department selectById(int id) throws SQLException {
        Department department = null;
        if (id < 1) {
            return department;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String name = rs.getString("DEPARTMENT_NAME");
            department = new Department(id, name);
        }
        rs.close();
        ps.close();
        return department;
    }

    public Department selectByName(@NonNull String name) throws SQLException {
        Department department = null;
        if (name.length() < 1) {
            return department;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME=?");
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int id = rs.getInt("DEPARTMENT_ID");
            department = new Department(id, name);
        }
        rs.close();
        ps.close();
        return department;
    }

    public Vector<Department> selectAll() throws SQLException {
        Vector<Department> departments = new Vector<>();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM DEPARTMENTS ORDER BY DEPARTMENT_ID");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("DEPARTMENT_ID");
            String name = rs.getString("DEPARTMENT_NAME");
            Department department = new Department(id, name);
            departments.addElement(department);
        }
        rs.close();
        ps.close();
        return departments;
    }

    public boolean update(int id, @NonNull String name) throws SQLException {
        if (name.length() < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("UPDATE DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?");
        ps.setString(1, name);
        ps.setInt(2, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public boolean delete(int id) throws SQLException {
        if (id < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID=?");
        ps.setInt(1, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
